Recursive CTE হল SQL Server-এ একটি বিশেষ ধরনের Common Table Expression (CTE) যা একটি কুয়েরি (Query) নিজেকে পুনরায় কল (Recursive) করতে সক্ষম। এটি ডেটাবেসের মধ্যে এমন সমস্যা সমাধানে সহায়তা করে যেখানে একটি হায়ারার্কিকাল (অথবা পিরামিডাল) ডেটা কাঠামো বা সম্পর্কের প্রয়োজন হয়, যেমন অর্গানাইজেশনাল চার্ট, ফোল্ডার স্ট্রাকচার, পরিবারের গাছ, ইত্যাদি।
Recursive CTE এর গঠন
একটি Recursive CTE সাধারণত তিনটি অংশে বিভক্ত:
- Anchor Member: এটি CTE এর প্রথম অংশ, যেখানে আপনি প্রথম মান বা ডেটা সেট নির্ধারণ করেন।
- Recursive Member: এটি CTE এর পুনরাবৃত্তিমূলক অংশ, যা নিজেকে পুনরায় কল করে এবং আগের আউটপুটের উপর ভিত্তি করে নতুন মান বা ডেটা তৈরি করে।
- Termination Condition: এটি CTE এর সীমাবদ্ধতা নির্ধারণ করে, যাতে এটি কখনও অগণিত রেকর্ড তৈরি না করে।
সাধারণ সিনট্যাক্স
WITH RecursiveCTE AS
(
-- Anchor Member
SELECT column1, column2
FROM table_name
WHERE condition1
UNION ALL
-- Recursive Member
SELECT t.column1, t.column2
FROM table_name t
INNER JOIN RecursiveCTE r
ON t.column1 = r.column1
WHERE condition2
)
SELECT * FROM RecursiveCTE;
উদাহরণ ১: অর্গানাইজেশনাল চার্ট (Hierarchical Data)
ধরা যাক, আপনার একটি Employees টেবিল রয়েছে, যেখানে কর্মীদের হায়ারার্কি সংরক্ষিত রয়েছে। ManagerID কলামে একজন কর্মীর Manager's ID রাখা হয়েছে, এবং EmployeeID হল কর্মীর একক আইডি। আপনি এই তথ্য থেকে কর্মীদের হায়ারার্কি (অর্গানাইজেশনাল চার্ট) বের করতে চান।
Employees টেবিলের গঠন:
| EmployeeID | Name | ManagerID |
|---|---|---|
| 1 | John Doe | NULL |
| 2 | Jane Smith | 1 |
| 3 | Sam Brown | 1 |
| 4 | Mike Johnson | 2 |
| 5 | Sarah Lee | 2 |
Recursive CTE ব্যবহার করে অর্গানাইজেশনাল চার্ট:
WITH RecursiveCTE AS
(
-- Anchor Member: Manager ছাড়া প্রথম স্তরের কর্মী নির্বাচন করা
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive Member: ম্যানেজারের অধীনে কর্মী নির্বাচন করা
SELECT e.EmployeeID, e.Name, e.ManagerID, r.Level + 1 AS Level
FROM Employees e
INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
-- ফলাফল নির্বাচন করা
SELECT EmployeeID, Name, ManagerID, Level
FROM RecursiveCTE
ORDER BY Level, ManagerID;
আউটপুট:
| EmployeeID | Name | ManagerID | Level |
|---|---|---|---|
| 1 | John Doe | NULL | 0 |
| 2 | Jane Smith | 1 | 1 |
| 3 | Sam Brown | 1 | 1 |
| 4 | Mike Johnson | 2 | 2 |
| 5 | Sarah Lee | 2 | 2 |
এখানে:
- Anchor Member প্রথমে ম্যানেজার ছাড়া কর্মীদের নির্বাচন করে।
- Recursive Member এরপর ম্যানেজারের অধীনে থাকা কর্মীদের নির্বাচন করে এবং Level বাড়িয়ে দেয়, এটি পুনরায় আগের রেকর্ডের সাথে যুক্ত হয়।
উদাহরণ ২: ফোল্ডার স্ট্রাকচার
ধরা যাক, আপনার একটি Folders টেবিল রয়েছে, যেখানে ফোল্ডার এবং সাবফোল্ডারের তথ্য রয়েছে। আপনি যদি ফোল্ডার স্ট্রাকচার (যেমন হায়ারার্কি) বের করতে চান, তাহলে Recursive CTE ব্যবহার করতে পারেন।
Folders টেবিলের গঠন:
| FolderID | FolderName | ParentFolderID |
|---|---|---|
| 1 | Root | NULL |
| 2 | Documents | 1 |
| 3 | Reports | 2 |
| 4 | Financial | 2 |
| 5 | Personal | 1 |
Recursive CTE ব্যবহার করে ফোল্ডার স্ট্রাকচার বের করা:
WITH RecursiveCTE AS
(
-- Anchor Member: মূল ফোল্ডার নির্বাচন করা (Root)
SELECT FolderID, FolderName, ParentFolderID, 0 AS Level
FROM Folders
WHERE ParentFolderID IS NULL
UNION ALL
-- Recursive Member: সাবফোল্ডারগুলো নির্বাচন করা
SELECT f.FolderID, f.FolderName, f.ParentFolderID, r.Level + 1 AS Level
FROM Folders f
INNER JOIN RecursiveCTE r ON f.ParentFolderID = r.FolderID
)
-- ফলাফল নির্বাচন করা
SELECT FolderID, FolderName, ParentFolderID, Level
FROM RecursiveCTE
ORDER BY Level, ParentFolderID;
আউটপুট:
| FolderID | FolderName | ParentFolderID | Level |
|---|---|---|---|
| 1 | Root | NULL | 0 |
| 2 | Documents | 1 | 1 |
| 5 | Personal | 1 | 1 |
| 3 | Reports | 2 | 2 |
| 4 | Financial | 2 | 2 |
এখানে:
- Anchor Member প্রথমে মূল ফোল্ডার (Root) নির্বাচন করে।
- Recursive Member এরপর সাবফোল্ডারগুলো নির্বাচন করে এবং Level বাড়িয়ে দেয়, যা সম্পূর্ণ ফোল্ডার স্ট্রাকচার তৈরি করতে সাহায্য করে।
সারাংশ
- Recursive CTE ডেটাবেসের মধ্যে হায়ারার্কিকাল ডেটা (যেমন: অর্গানাইজেশনাল চার্ট, ফোল্ডার স্ট্রাকচার) বের করার জন্য ব্যবহৃত হয়।
- এটি Anchor Member দিয়ে শুরু হয় এবং পরবর্তীতে Recursive Member দিয়ে নিজেকে পুনরায় কল করে।
- এটি অত্যন্ত কার্যকরী যখন আপনি ডেটাবেসের মধ্যে সম্পর্কিত তথ্যের মধ্যে হায়ারার্কি তৈরি করতে চান।
Read more
